package edu.ynu.MyProject.dao;

import edu.ynu.MyProject.common.dao.LogicDAO;
import edu.ynu.MyProject.entity.Data;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface IDataDao extends LogicDAO<Data, Long> {
    @Query("SELECT dd FROM Data dd " +
            "JOIN FETCH dd.equipment d " +
            "JOIN FETCH d.greenhouse g " +
            "WHERE dd.dataType!=18 AND dd.createTime = (SELECT MAX(dd2.createTime) FROM Data dd2 WHERE dd2.equipment = d) " +
            "AND g.id = :greenhouseId")
    List<Data> findLatestDataByGreenhouse(@Param("greenhouseId") Long greenhouseId);

    @Query("SELECT d.id FROM Equipment d WHERE d.greenhouse.id = :greenhouseId AND d.type = 'camera'")
    Long findDeviceIdByGreenhouseIdAndType(@Param("greenhouseId") Long greenhouseId);

    @Query(value = "SELECT AVG(dd.value) " +
            "FROM data dd " +
            "JOIN equipment d ON dd.equipment_id = d.id " +
            "JOIN green_house_info g ON d.greenhouse_id = g.id " +
            "WHERE g.id = :gid " +
            "AND dd.name = '土壤温度' " +
            "AND DATE(dd.create_time) = :date", nativeQuery = true)
    Double findAverageSoilTemperatureByGreenhouseAndDate(@Param("gid") Long gid, @Param("date") String date);

    // 查询大棚ID下所有设备，某天的空气温度的平均值
    @Query(value = "SELECT AVG(dd.value) " +
            "FROM data dd " +
            "JOIN equipment d ON dd.equipment_id = d.id " +
            "JOIN green_house_info g ON d.greenhouse_id = g.id " +
            "WHERE g.id = :gid " +
            "AND dd.name = '空气温度' " +
            "AND DATE(dd.create_time) = :date", nativeQuery = true)
    Double findAverageAirTemperatureByGreenhouseAndDate(@Param("gid") Long gid, @Param("date") String date);

    @Query(value = "SELECT dd.image_data FROM data dd " +
            "JOIN equipment d ON dd.equipment_id = d.id " +
            "JOIN green_house_info g ON d.greenhouse_id = g.id " +
            "WHERE dd.data_type = 18 " +
            "AND g.id = :greenhouseId " +
            "ORDER BY dd.create_time DESC LIMIT 1", nativeQuery = true)
    byte[] findLatestImagesByDataTypeAndGreenhouseId(@Param("greenhouseId") Long greenhouseId);
}
